#!/usr/bin/env python
# encoding: utf-8
'''
@author: liuchong
@contact: lucas@007vin.com
@software: garner
@file: banzhou.py
@time: 2020/9/9 2:44 下午
@desc:
'''
import re
import pandas as pd

import ujson

from models.skoda.skoda import CarsPartNumNew, CarsStructureCollectionNew
from models.skoda.skoda_sa import CarsSaDesc
from models.skoda.skoda_vb import Vb
from scripts.fenci import remove_tt

skoda_parts = CarsPartNumNew.select(CarsPartNumNew.pid).where(CarsPartNumNew.pid != '').distinct()
s = ['2缸', '二缸', '3缸', '三缸', '4缸', '四缸', '5缸', '五缸', '6缸', '六缸', '8缸', '八缸', '10缸', '十缸', '12缸', '十二缸']
qd = ['全轮驱动', '前驱动', '前部驱动', '四轮驱动']
bsx = ['7档', '7档自动', '7档手动', '七档', '七档自动', '七档手动', '6档', '6档自动', '6档手动',
       '六档', '六档自动', '六档手动', '5档', '5档自动', '5档手动', '五档', '五档自动', '五档手动',
       '4档', '4档自动', '4档手动', '四档', '四档自动', '四档手动', '8档', '8档自动', '8档手动',
       '八档', '八档自动', '八档手动', '3档', '3档自动', '3档手动', '三档', '三档自动', '三档手动', '无级自动']
pl = ['1.6', '1.8', '1.3', '2.0', '2.1', '1.9', '1.7', '2.2', '2.23', '2.3', '1.5', '0.9', '1.1',
      '0.8', '2.8', '2.6', '1.4', '4.2', '3.6', '2.5', '2.4', '3.7', '2.7', '1.2', '3.0', '3.3',
      '0.6', '3.2', '0.4', '5.2', '2.9', '0.1', '0.5', '6.3']
fdjdm = ['AEE', 'AEF', 'AMG', 'AMH', 'AMJ', 'AEH', 'AGN', 'AGP', 'AGR', 'AGU', 'AHF', 'AKL', 'AMD', 'APK', 'AQY', 'ARZ', 'ASV', 'AUM', 'AZH', 'AME', 'AMF', 'AQV', 'AQW', 'ARV', 'ASY', 'ASZ', 'ATD', 'ATZ', 'AUA', 'AUB', 'AWY', 'AZE', 'AZF', 'AZL', 'AZQ', 'BBY', 'BBZ', 'BKY', 'BMD', 'AEG', 'ALH', 'AQM', 'ARX', 'AUQ', 'AVU', 'AXP', 'AXR', 'AZJ', 'BCA', 'BFQ', 'AMX', 'AVB', 'AVF', 'AWT', 'AWX', 'AYM', 'AZM', 'BBG', 'BDG', 'BPZ', 'BSS', 'BSV', 'BWW', 'AZV', 'BGU', 'BJB', 'BKC', 'BKD', 'BLF', 'BLR', 'BLS', 'BLX', 'BLY', 'BMM', 'BMN', 'BSE', 'BSF', 'BUD', 'BVX', 'BVY', 'BVZ', 'BWA', 'BXE', 'BZB', 'CCSA', 'CEGA', 'CSSA', 'DJNB', 'DJSA', 'DLSA', 'CZCA', 'CZDA', 'CZPA', 'DBGC', 'BLT', 'BME', 'BNM', 'BNV', 'BMS', 'BSW', 'BTS', 'BXW', 'BZG', 'CGPA', 'EBMA', 'CWVA', 'DADA', 'DBYA', 'DGTA', 'DGTD', 'DKLA', 'DKRF', 'DLAA', 'DLAC', 'DPCA', 'CRVC', 'CZPB', 'DGEA', 'DHFA', 'DJKA', 'DLBA', 'DNNA', 'DNPA', 'DSTA', 'DSTB', 'DSUD', 'DTRD', 'DTTA', 'DTTC', 'DTUA', 'DLFA', 'DLXA', 'BBM', 'CEVA', 'CHFA', 'CHTA', 'BXX', 'BYJ', 'CDFA', 'CEAA', 'CFBA', 'CGMA', 'CGZA', 'CLRA', 'CPJA', 'BMP', 'CAXC', 'CAYC', 'CBBB', 'CCZA', 'CDAA', 'CDAB', 'CDVA', 'CFFB', 'CFGB', 'CLJA', 'CUFA', 'CUGA', 'DBFC', 'DKVB', 'DKXA', 'DPLA', 'CDDA', 'CDEA', 'CLPA', 'CLSA', 'CAXA', 'CBZB', 'CFHC', 'CFHF', 'CGGA', 'CHGA', 'CLCA', 'CLCB', 'CMXA', 'CBDB', 'CFHA', 'CFJA', 'CAVE', 'CAYA', 'CAYB', 'CBZA', 'CFNA', 'CFWA', 'CGGB', 'CGPB', 'CJLB', 'CTHE', 'CJLA', 'CLNA', 'CWXB', 'CWXC', 'DSHA', 'CHYA', 'CHYB', 'CPGA', 'CGPC', 'CHZB', 'CHZC', 'CJZC', 'CJZD', 'CUSB', 'CWVB', 'CXMA', 'DKLD', 'DKRC', 'CHHA', 'CHHB', 'CHPA', 'CHPB', 'CHZD', 'CJSA', 'CJSB', 'CJZA', 'CJZB', 'CKFB', 'CKFC', 'CLHA', 'CLHB', 'CPWA', 'CRKA', 'CRKB', 'CRMB', 'CUNA', 'CUPA', 'CXXA', 'CXXB', 'CYKA', 'CYVA', 'CYVB', 'DBKA', 'CKAA', 'CPDA', 'CSRA', 'CUUA', 'CUUB', 'DFSA', 'DFSB', 'CSTA', 'DLWA', 'DMBA', 'CHYE', 'CUSA', 'CUTA', 'CRGB', 'CUAA', 'CZEA', 'DFGA', 'DFGC', 'DFHA', 'DKZA', 'DTSB', 'DCYA', 'DDYA', 'DDYB', 'DFFA', 'DGTB', 'DGTE', 'DHGA', 'DJGA', 'DKRA', 'DKTB', 'CHZJ', 'CJSC', 'CJXA', 'CRGA', 'CRLB', 'CVRA', 'DCXA', 'DCZA', 'DDAA', 'DFCA', 'DFEA', 'DGEB', 'DLKB', 'DNFE', 'DNUA', 'DSRA', 'DSRB', 'DSUB', 'DTRA', 'DTSA', 'DAHA']

item = []
flag = 0
for a in skoda_parts:
    # 配置代码
    sa_codes = []
    # 发动机排量
    cylinder = []
    # 发动机代码
    vb = []
    # 发动机排量信息
    vb_desc = []

    model_desc = []
    transmission = []
    drive = []
    diff_mech = []
    replace = []
    # 匹配信息

    # 处理audi_copy表中的label字段

    parts = CarsPartNumNew.select(CarsPartNumNew.cid, CarsPartNumNew.sa_code, CarsPartNumNew.mid, CarsPartNumNew.label,
                                  CarsPartNumNew.model, CarsPartNumNew.remark).filter(CarsPartNumNew.pid == a.pid)
    for part in parts:
        cid = part.cid
        label = part.label
        if label:
            label = re.findall(r'[\u4e00-\u9fa5]', label)
            label = ''.join(label)
        audi_structure = CarsStructureCollectionNew.get_or_none(CarsStructureCollectionNew.cid == cid, CarsStructureCollectionNew.mid == part.mid)
        if audi_structure:
            structure_description = audi_structure.description or ''
            structure_sa_codes = audi_structure.sa_code or '{}'
            structure_sa_codes = ujson.loads(structure_sa_codes)
            for key, value in structure_sa_codes.items():
                if value:
                    sa_codes = sa_codes + value

            structure_model = audi_structure.model
            structure_model = structure_model.replace('<br/>', '')

            # 筛选出structure表model字段中的缸数和排量信息
            structure_out = remove_tt(structure_model)
            # 筛选出structure表description字段中的缸数和排量信息
            structure_description_out = remove_tt(structure_description)

            if structure_description:
                for ind, key in enumerate(s):
                    if key in structure_description_out:
                        key = key.replace('二缸', '2')
                        key = key.replace('2缸', '2')
                        key = key.replace('三缸', '3')
                        key = key.replace('3缸', '3')
                        key = key.replace('四缸', '4')
                        key = key.replace('4缸', '4')
                        key = key.replace('五缸', '5')
                        key = key.replace('5缸', '5')
                        key = key.replace('六缸', '6')
                        key = key.replace('6缸', '6')
                        key = key.replace('八缸', '8')
                        key = key.replace('8缸', '8')
                        key = key.replace('十缸', '10')
                        key = key.replace('10缸', '10')
                        key = key.replace('十二缸', '12')
                        key = key.replace('12缸', '12')
                        cylinder.append(key)

                for key in pl:
                    if key in structure_description:
                        vb_desc.append(key)
            if structure_model:
                for key in bsx:
                    if key in structure_out:
                        key = key.replace('三', '3')
                        key = key.replace('四', '4')
                        key = key.replace('五', '5')
                        key = key.replace('六', '6')
                        key = key.replace('七', '7')
                        key = key.replace('八', '8')
                        key = key.replace('档', '挡')
                        if key == '5挡':
                            key = '5挡手动'
                            if '5挡-5挡手动' not in replace:
                                replace.append('5挡-5挡手动')
                        if key == '6挡':
                            key = '6挡手动'
                            if '6挡-6挡手动' not in replace:
                                replace.append('6挡-6挡手动')
                        if key == '6挡自动':
                            key = '6挡AMT'
                            if '6挡自动-6挡AMT' not in replace:
                                replace.append('6挡自动-6挡AMT')
                        if key == '7挡自动':
                            key = '7挡双离合'
                            if '7挡自动-7挡双离合' not in replace:
                                replace.append('7挡自动-7挡双离合')
                        if key == '无级自动':
                            key = 'CVT无级变速'
                            if '无级自动-CVT无级变速' not in replace:
                                replace.append('无级自动-CVT无级变速')
                        transmission.append(key)

                for key in qd:
                    if key in structure_out:
                        key = key.replace('前驱动', '前置前驱')
                        key = key.replace('前部驱动', '前置前驱')
                        key = key.replace('四轮驱动', '全轮驱动')
                        drive.append(key)

                for ind, key in enumerate(s):
                    if key in structure_out:
                        key = key.replace('二缸', '2')
                        key = key.replace('2缸', '2')
                        key = key.replace('三缸', '3')
                        key = key.replace('3缸', '3')
                        key = key.replace('四缸', '4')
                        key = key.replace('4缸', '4')
                        key = key.replace('五缸', '5')
                        key = key.replace('5缸', '5')
                        key = key.replace('六缸', '6')
                        key = key.replace('6缸', '6')
                        key = key.replace('八缸', '8')
                        key = key.replace('8缸', '8')
                        key = key.replace('十缸', '10')
                        key = key.replace('10缸', '10')
                        key = key.replace('十二缸', '12')
                        key = key.replace('12缸', '12')
                        cylinder.append(key)
                for key in pl:
                    if key in structure_model:
                        vb_desc.append(key)
                for key in fdjdm:
                    if key in structure_model:
                        vb.append(key)
                if 'PR:' in structure_model or 'PR-' in structure_model or 'pr:' in structure_model or 'pr-' in structure_model:
                    # 处理structure_model中的PR
                    structure_pr = re.sub('[\u4e00-\u9fa5]', '', structure_model)
                    structure_pr = structure_pr.replace('PR:', '')
                    structure_pr = structure_pr.replace('PR-', '')
                    structure_pr = structure_pr.replace('+', ',')
                    structure_pr = structure_pr.replace('/', ',')
                    structure_pr = structure_pr.replace(':', ',')
                    structure_pr = structure_pr.replace('_', ',')
                    structure_pr = structure_pr.split(',')
                    for i in structure_pr:
                        sa_codes.append(i)
            # else:
            #     # 处理structure表发动机代码对应的排量信息
            #     structure_pr = structure_model.replace('+', ',')
            #     structure_pr = structure_pr.replace(':', ',')
            #     structure_pr = structure_pr.replace('_', ',')
            #     structure_pr = structure_pr.split(',')
            #     for i in structure_pr:
            #         vb.append(i)

        part_sa_codes = part.sa_code or '{}'

        part_sa_codes = ujson.loads(part_sa_codes)

        part_remark = part.remark or ''
        # 处理audi part表和structure表的sa_code

        for key, value in part_sa_codes.items():
            if value:
                sa_codes = sa_codes+value
        # print(sa_codes)

        #处理structure表和part表的model字段

        part_model = part.model
        part_model = part_model.replace('<br/>', '')

        # 筛选出structure表model字段中的缸数和排量信息
        part_out = remove_tt(part_model)
        part_remark_out = remove_tt(part_remark)

        if part_remark:
            for ind, key in enumerate(s):
                if key in part_remark_out:
                    key = key.replace('二缸', '2')
                    key = key.replace('2缸', '2')
                    key = key.replace('三缸', '3')
                    key = key.replace('3缸', '3')
                    key = key.replace('四缸', '4')
                    key = key.replace('4缸', '4')
                    key = key.replace('五缸', '5')
                    key = key.replace('5缸', '5')
                    key = key.replace('六缸', '6')
                    key = key.replace('6缸', '6')
                    key = key.replace('八缸', '8')
                    key = key.replace('8缸', '8')
                    key = key.replace('十缸', '10')
                    key = key.replace('10缸', '10')
                    key = key.replace('十二缸', '12')
                    key = key.replace('12缸', '12')
                    cylinder.append(key)

            for key in pl:
                if key in part_remark:
                    vb_desc.append(key)
        if part_model:
            for key in bsx:
                if key in part_out:
                    key = key.replace('三', '3')
                    key = key.replace('四', '4')
                    key = key.replace('五', '5')
                    key = key.replace('六', '6')
                    key = key.replace('七', '7')
                    key = key.replace('八', '8')
                    key = key.replace('档', '挡')
                    if key == '5挡':
                        key = '5挡手动'
                        if '5挡-5挡手动' not in replace:
                            replace.append('5挡-5挡手动')
                    if key == '6挡':
                        key = '6挡手动'
                        if '6挡-6挡手动' not in replace:
                            replace.append('6挡-6挡手动')
                    if key == '6挡自动':
                        key = '6挡AMT'
                        if '6挡自动-6挡AMT' not in replace:
                            replace.append('6挡自动-6挡AMT')
                    if key == '7挡自动':
                        key = '7挡双离合'
                        if '7挡自动-7挡双离合' not in replace:
                            replace.append('7挡自动-7挡双离合')
                    if key == '无级自动':
                        key = 'CVT无级变速'
                        if '无级自动-CVT无级变速' not in replace:
                            replace.append('无级自动-CVT无级变速')
                    transmission.append(key)
            for key in qd:
                if key in part_out:
                    key = key.replace('前驱动', '前置前驱')
                    key = key.replace('前部驱动', '前置前驱')
                    key = key.replace('四轮驱动', '全轮驱动')
                    drive.append(key)

            for ind, key in enumerate(s):
                if key in part_out:
                    key = key.replace('二缸', '2')
                    key = key.replace('2缸', '2')
                    key = key.replace('三缸', '3')
                    key = key.replace('3缸', '3')
                    key = key.replace('四缸', '4')
                    key = key.replace('4缸', '4')
                    key = key.replace('五缸', '5')
                    key = key.replace('5缸', '5')
                    key = key.replace('六缸', '6')
                    key = key.replace('6缸', '6')
                    key = key.replace('八缸', '8')
                    key = key.replace('8缸', '8')
                    key = key.replace('十缸', '10')
                    key = key.replace('10缸', '10')
                    key = key.replace('十二缸', '12')
                    key = key.replace('12缸', '12')
                    cylinder.append(key)
            for key in pl:
                if key in part_model:
                    vb_desc.append(key)
            for key in fdjdm:
                if key in part_model:
                    vb.append(key)
            if 'PR:' in part_model or 'PR-' in part_model or 'pr:' in part_model or 'pr-' in part_model:
                # 处理part_model中的PR
                part_pr = re.sub('[\u4e00-\u9fa5]', '', part_model)
                part_pr = part_pr.replace('PR:', '')
                part_pr = part_pr.replace('PR-', '')
                part_pr = part_pr.replace('+', ',')
                part_pr = part_pr.replace('/', ',')
                part_pr = part_pr.replace(':', ',')
                part_pr = part_pr.replace('_', ',')
                part_pr = part_pr.split(',')
                for j in part_pr:
                    sa_codes.append(j)
        # else:
        #     # 处理part表发动机代码对应的排量信息
        #     part_pr = part_model.replace('+', ',')
        #     part_pr = part_pr.replace('/', ',')
        #     part_pr = part_pr.replace(':', ',')
        #     part_pr = part_pr.replace('_', ',')
        #     part_pr = part_pr.split(',')
        #     for i in part_pr:
        #         vb.append(i)

        sa_codes = set(sa_codes)
        sa_codes = list(sa_codes)

        vb = set(vb)
        vb = list(vb)
        # print(vb)



        # 筛选sa_code对应的描述信息
        for i in sa_codes:
            contrast = CarsSaDesc.get_or_none(CarsSaDesc.sa == i, CarsSaDesc.lang == 'zh', CarsSaDesc.brand_code == 'skoda')
            if contrast and contrast.desc:
                if contrast.desc not in model_desc:
                    model_desc.append(contrast.desc)


        # 筛选model中发动机代码对应的排量信息
        for i in vb:
            # print(Vb.aa.zfill(3))
            # break
            if cid.isdigit():
                cid = str(int(cid))
            vb_AF = Vb.get_or_none(Vb.aa == cid, Vb.ab.contains(i), Vb.brand == 'skoda')
            if vb_AF and vb_AF.af:
                # print(int(vb_AF.af)/100)
                # break
                vb_desc.append(str(int(vb_AF.af)/100))

        for i in model_desc:
            # 处理sa_code中对应的发动机配置描述信息
            if '发动机' in i:
                # 筛选出sa表desc中的排量信息
                for key in pl:
                    if key in i:
                        vb_desc.append(key)
                for key in s:
                    if key in i:
                        key = key.replace('二缸', '2')
                        key = key.replace('2缸', '2')
                        key = key.replace('三缸', '3')
                        key = key.replace('3缸', '3')
                        key = key.replace('四缸', '4')
                        key = key.replace('4缸', '4')
                        key = key.replace('五缸', '5')
                        key = key.replace('5缸', '5')
                        key = key.replace('六缸', '6')
                        key = key.replace('6缸', '6')
                        key = key.replace('八缸', '8')
                        key = key.replace('8缸', '8')
                        key = key.replace('十缸', '10')
                        key = key.replace('10缸', '10')
                        key = key.replace('十二缸', '12')
                        key = key.replace('12缸', '12')
                        if key not in cylinder:
                            cylinder.append(key)

            # 处理变速器配置
            if ('变速箱' in i or '变速器' in i) and ('档' in i or '无级' in i):
                i = i.replace('-', '')
                i = i.replace(' ', '')
                i = i.replace('变速箱', '')
                i = i.replace('变速器', '')
                i = i.replace('全轮驱动', '')
                i = i.replace('用于', '')
                i = i.replace('用', '')
                i = i.replace('的', '')
                i = i.replace('三', '3')
                i = i.replace('四', '4')
                i = i.replace('五', '5')
                i = i.replace('六', '6')
                i = i.replace('七', '7')
                i = i.replace('八', '8')
                i = i.replace('档', '挡')
                i = i.replace('（最低与最高挡传动比比值不同）', '')
                if i == '5挡':
                    i = '5挡手动'
                    if '5挡-5挡手动' not in replace:
                        replace.append('5挡-5挡手动')
                if i == '6挡':
                    i = '6挡手动'
                    if '6挡-6挡手动' not in replace:
                        replace.append('6挡-6挡手动')
                if i == '6挡自动':
                    i = '6挡AMT'
                    if '6挡自动-6挡AMT' not in replace:
                        replace.append('6挡自动-6挡AMT')
                if i == '7挡自动':
                    i = '7挡双离合'
                    if '7挡自动-7挡双离合' not in replace:
                        replace.append('7挡自动-7挡双离合')
                if i == '无级自动':
                    i = 'CVT无级变速'
                    if '无级自动-CVT无级变速' not in replace:
                        replace.append('无级自动-CVT无级变速')
                transmission.append(i)

            # 处理驱动配置
            for key in qd:
                if key in i:
                    key = key.replace('前驱动', '前置前驱')
                    key = key.replace('前部驱动', '前置前驱')
                    key = key.replace('四轮驱动', '全轮驱动')
                    drive.append(key)

            # 处理差速器配置
            if '差速' in i or '减速' in i:
                diff_mech.append(i)
    # print(vb)
    vb_desc = set(vb_desc)
    vb_desc = list(vb_desc)
    vb_desc = ','.join(vb_desc)

    cylinder = set(cylinder)
    cylinder = list(cylinder)
    cylinder = ','.join(cylinder)

    transmission = set(transmission)
    transmission = list(transmission)
    transmission = ','.join(transmission)

    drive = set(drive)
    drive = list(drive)
    drive = ','.join(drive)

    diff_mech = set(diff_mech)
    diff_mech = list(diff_mech)
    diff_mech = ','.join(diff_mech)

    model_desc = ','.join(model_desc)
    vb = ','.join(vb)
    replace = ','.join(replace)
    unit = {'pid': a.pid, 'label': label, 'desc': model_desc, 'engine_code': vb,
                 'cylinder': cylinder, 'transmission': transmission, 'replace': replace, 'drive': drive,
                 '差速器': diff_mech, 'displacement': vb_desc}
    print(unit)
    item.append(unit)

df = pd.DataFrame(item)
df.to_csv('/Users/liuchong/standartools/cars_scripts/skoda/export/skoda_all01.csv', mode='a', index=False)